﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Linq;
using System.Text;

namespace MDF2SQLITE
{
    class Program
    {
        static void Main(string[] args)
        {
            //SqlBulkCopy

            string mdffile = @"E:\SHARE\大数据\FCDBackup20100900.mdf";
            string sdbfile = @"E:\SHARE\大数据\FCDBackup20100900.db";

            string mdfConnectionString = string.Format(@"Data Source=(LocalDB)\v11.0;AttachDbFilename='{0}';Integrated Security=True;Connect Timeout=30", mdffile);
            string sdbConnectionString = string.Format(@"Data Source='{0}'", sdbfile);

            SqlConnection mdfConn = new SqlConnection(mdfConnectionString);
            SQLiteConnection sdbConn = new SQLiteConnection(sdbConnectionString);
            
            mdfConn.Open();
            sdbConn.Open();

            ConvertDB(mdfConn, sdbConn);

            mdfConn.Close();
            sdbConn.Clone();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="mdfConn"></param>
        /// <param name="sdbConn"></param>
        private static void ConvertDB(SqlConnection mdfConn, SQLiteConnection sdbConn)
        {
            // 建表
            string sql =
                        "CREATE TABLE IF NOT EXISTS VehicleData20100901 (" +
                        "    ID             INT          IDENTITY (1, 1) NOT NULL," +
                        "    CompanyID      VARCHAR (50) NULL," +
                        "    VehicleSimID   VARCHAR (50) NOT NULL," +
                        "    GPSTime        DATETIME     NOT NULL," +
                        "    GPSLongitude   FLOAT (53)   NOT NULL," +
                        "    GPSLatitude    FLOAT (53)   NOT NULL," +
                        "    GPSSpeed       FLOAT (53)   NOT NULL," +
                        "    GPSDirection   FLOAT (53)   NOT NULL," +
                        "    PassengerState INT          NOT NULL," +
                        "    ReadFlag       INT          NOT NULL," +
                        "    CreateDate     DATETIME     NOT NULL" +
                        ")";
            SQLiteHelper.ExecuteNonQuery(sdbConn, CommandType.Text, sql);
            SQLiteTransaction tran = sdbConn.BeginTransaction();

            int count = (int)SQLServerHelper.ExecuteScalar(mdfConn, CommandType.Text, "select count(*) from VehicleData20100901");

            Console.WriteLine("总共: {0}", count);
            sql = string.Format("select ID, CompanyID, VehicleSimID, GPSTime, GPSLongitude, GPSLatitude, GPSSpeed, GPSDirection, PassengerState, ReadFlag, CreateDate from VehicleData20100901");
            using (SqlDataReader reader = SQLServerHelper.ExecuteReader(mdfConn, CommandType.Text, sql))
            {
                int i = 0;
                for (bool has = reader.Read(); has != false; has = reader.Read()) {

                    sql = string.Format("insert into VehicleData20100901(ID, CompanyID, VehicleSimID, GPSTime, GPSLongitude, GPSLatitude, GPSSpeed, GPSDirection, PassengerState, ReadFlag, CreateDate) " +
                                        "VALUES(@ID, @CompanyID, @VehicleSimID, @GPSTime, @GPSLongitude, @GPSLatitude, @GPSSpeed, @GPSDirection, @PassengerState, @ReadFlag, @CreateDate)");
                    
                    SQLiteParameter[] args = new SQLiteParameter[11];
                    args[0] = new SQLiteParameter("@ID", reader["ID"]);
                    args[1] = new SQLiteParameter("@CompanyID", reader["CompanyID"]);
                    args[2] = new SQLiteParameter("@VehicleSimID", reader["VehicleSimID"]);
                    args[3] = new SQLiteParameter("@GPSTime", reader["GPSTime"]);
                    args[4] = new SQLiteParameter("@GPSLongitude", reader["GPSLongitude"]);
                    args[5] = new SQLiteParameter("@GPSLatitude", reader["GPSLatitude"]);
                    args[6] = new SQLiteParameter("@GPSSpeed", reader["GPSSpeed"]);
                    args[7] = new SQLiteParameter("@GPSDirection", reader["GPSDirection"]);
                    args[8] = new SQLiteParameter("@PassengerState", reader["PassengerState"]);
                    args[9] = new SQLiteParameter("@ReadFlag", reader["ReadFlag"]);
                    args[10] = new SQLiteParameter("@CreateDate", reader["CreateDate"]);

                    SQLiteHelper.ExecuteNonQuery(tran, CommandType.Text, sql, args);

                    if (++i % 100000 == 0) {
                        tran.Commit();
                        tran = sdbConn.BeginTransaction();
                        Console.WriteLine("{0}/{1}: {2:0.00}", i, count, (double)i * 100 / count);
                    }

                }
                
                
                
                tran.Commit();
            }

        }
    }
}
